package com.gopay.common.asynexport.service.impl;

import com.alibaba.fastjson.JSONObject;
import com.gopay.common.BankOrderTranslateUtil;
import com.gopay.common.PageInfo;
import com.gopay.common.asynexport.constants.AsynexportConstants;
import com.gopay.common.asynexport.constants.FunctionCodeEnum;
import com.gopay.common.asynexport.dao.AsynexportDao;
import com.gopay.common.asynexport.dao.AsynexportQueryDAO;
import com.gopay.common.asynexport.service.AsynexportService;
import com.gopay.common.asynexport.util.BytesUtil;
import com.gopay.common.asynexport.util.DicTranslate;
import com.gopay.common.asynexport.util.OperLogTranslateHelper;
import com.gopay.common.asynexport.vo.AsyncExportInfo;
import com.gopay.common.cipher.utils.SensitiveInfoUtils;
import com.gopay.common.constants.approval.DecisionValue;
import com.gopay.common.constants.autosettle.AutoCustTypeEnum;
import com.gopay.common.constants.autosettle.AutoSettleCheckOrderConstants;
import com.gopay.common.constants.autosettle.DefaultAutoSettleLimitEnum;
import com.gopay.common.constants.autosettle.SettlementExchangeConstants;
import com.gopay.common.constants.cps.AcctType;
import com.gopay.common.constants.cps.OnlineRelaunch;
import com.gopay.common.constants.cps.PayStat;
import com.gopay.common.constants.cust.CustPersonInfoConstants;
import com.gopay.common.constants.cust.CustType;
import com.gopay.common.constants.cust.UserSourceConstans;
import com.gopay.common.constants.microdone.PwdCtrlFingerTypeConstants;
import com.gopay.common.constants.riskcontrol.RiskControlConstants;
import com.gopay.common.constants.trans.PayChannel;
import com.gopay.common.constants.trans.TxnStaCode;
import com.gopay.common.constants.txncd.IntTxnCd;
import com.gopay.common.datasource.DBType;
import com.gopay.common.datasource.DbContextHolder;
import com.gopay.common.order.dao.CommonQueryDAO;
import com.gopay.common.util.DateUtils;
import com.gopay.common.util.JsonUtils;
import com.gopay.common.util.MoneyUtil;
import com.gopay.common.util.StringTools;
import org.apache.commons.lang.StringUtils;
import org.apache.commons.lang3.math.NumberUtils;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFFont;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.Font;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.xssf.streaming.SXSSFWorkbook;
import org.apache.tools.zip.ZipOutputStream;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.jdbc.UncategorizedSQLException;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;

import javax.annotation.Resource;
import java.io.BufferedOutputStream;
import java.io.File;
import java.io.FileOutputStream;
import java.math.BigDecimal;
import java.math.RoundingMode;
import java.sql.Timestamp;
import java.text.DateFormat;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Locale;
import java.util.Map;
import java.util.Random;

/**
 * Created by zhoutong on 2017/8/9.
 */
@Service("asynexportService")
public class AsynexportServiceImpl implements AsynexportService {
    private final Logger logger = LoggerFactory.getLogger(getClass());
    @Resource(name = "asynexportDao")
    private AsynexportDao asynexportDao;

    @Resource(name="asynexportQueryDAO")
    private AsynexportQueryDAO asynexportQueryDAO;

    @Resource(name = "commonQueryDAO")
    private CommonQueryDAO commonQueryDAO;

    @Resource(name = "asynexportExcelModel")
    private Map<String, String> asynexportExcelModel;

    @Resource(name = "operLogTranslateHelper")
    private OperLogTranslateHelper operLogTranslateHelper;

    /**
     * 通过sql和参数获取总记录数
     * @param queryCountSql
     * @param queryParam
     * @param dbType
     * @return
     */
    @Override
    public int getTotalCount(String queryCountSql, Map<String, Object> queryParam, String dbType) {
        logger.error("DBType:"+dbType);
        if (StringUtils.isNotBlank(dbType)) {
            DbContextHolder.setDbType(dbType);
        } else {
            DbContextHolder.setDbType(DBType.DS_MAIN.value);
        }
        //logger.info("countSql:"+queryCountSql);
        return asynexportQueryDAO.findCountBySql(queryCountSql, queryParam);
    }

    /**
     * 分页查询
     * @param functionCode
     * @param querySql
     * @param queryMap
     * @param pageInfo
     * @param dbType
     * @return
     */
    @Override
    public List<Object[]> query(String functionCode, String querySql, Map<String, Object> queryMap, PageInfo pageInfo, String dbType) {
        if (StringUtils.isNotBlank(dbType)) {
            DbContextHolder.setDbType(dbType);
        } else {
            DbContextHolder.setDbType(DBType.DS_MAIN.value);
        }
        //logger.info("querySql:"+querySql);
        List<Object[]> dataList = asynexportQueryDAO.findBySql(querySql, queryMap, pageInfo.getPageNum(), pageInfo.getPageSize());
        return formatDataList(functionCode, dataList);
    }

    /**
     * 对查询结果进行转换
     * @param functionCode
     * @param dataList
     */
    private List<Object[]> formatDataList(String functionCode, List<Object[]> dataList) {
        if (FunctionCodeEnum.FUNCTION_CODE_100.value.equals(functionCode)) {
            return format100Date(dataList);
        } else if(FunctionCodeEnum.FUNCTION_CODE_101.value.equals(functionCode)) {
            return format101Data(dataList);
        } else if(FunctionCodeEnum.FUNCTION_CODE_102.value.equals(functionCode)) {
            return format102Data(dataList);
        } else if(FunctionCodeEnum.FUNCTION_CODE_103.value.equals(functionCode)) {
            return format103Data(dataList);
        } else if(FunctionCodeEnum.FUNCTION_CODE_104.value.equals(functionCode)) {
            return format104Data(dataList);
        } else if(FunctionCodeEnum.FUNCTION_CODE_105.value.equals(functionCode)) {
            return format105Data(dataList);
        } else if(FunctionCodeEnum.FUNCTION_CODE_106.value.equals(functionCode)) {
            return format106Data(dataList);
        } else if(FunctionCodeEnum.FUNCTION_CODE_107.value.equals(functionCode)) {
            return format107Data(dataList);
        } else if(FunctionCodeEnum.FUNCTION_CODE_108.value.equals(functionCode)) {
            return format108Data(dataList);
        } else if(FunctionCodeEnum.FUNCTION_CODE_109.value.equals(functionCode)) {
            return format109Data(dataList);
        } else if(FunctionCodeEnum.FUNCTION_CODE_110.value.equals(functionCode)) {
            return format110Data(dataList);
        } else if(FunctionCodeEnum.FUNCTION_CODE_112.value.equals(functionCode)) {
            return format112Data(dataList);
        } else if(FunctionCodeEnum.FUNCTION_CODE_113.value.equals(functionCode)) {
            return format113Data(dataList);
        }else if(FunctionCodeEnum.FUNCTION_CODE_114.value.equals(functionCode)){
            return format114Data(dataList);
        }else if(FunctionCodeEnum.FUNCTION_CODE_115.value.equals(functionCode)){
            return format115Data(dataList);
        }



        else {
            throw new RuntimeException("未知的功能代码！！"+functionCode);
        }
    }


    /**
     * 风险查询
     */
    private List<Object[]> format114Data(List<Object[]> dataList){
        List<Object[]> result = new ArrayList<>();
        for (Object[] singleRowResult : dataList) {
            Object[] obj = new Object[16];
            //触发时间
            obj[0] =  StringTools.checkNulltoString(singleRowResult[0]);
            // 交易时间
            obj[1] =  StringTools.checkNulltoString(singleRowResult[1]);
            //国付宝订单号
            obj[2] =  StringTools.checkNulltoString(singleRowResult[2]);
            // 客户名称
            if(singleRowResult[3]!=null){
                obj[3] =  singleRowResult[3];
            } else {
                obj[3] = "";
            }
            // 客户类型
            if(singleRowResult[4]!=null){
                if(CustType.PERSON.toString().equals(singleRowResult[4].toString())){
                    obj[4] = "个人";
                }else{
                    obj[4] = "企业";
                }
            } else {
                obj[4] = "";
            }
            // 交易类型
            obj[5] = IntTxnCd.getIntTxnCdName(StringTools.checkNulltoString(singleRowResult[5]));
            // 支付方式
            obj[6] = PayChannel.translate(StringTools.checkNulltoString(singleRowResult[6]));
            // 交易金额
            obj[7] = StringTools.checkNulltoString(singleRowResult[7]);

            if(singleRowResult[12] != null && "0".equals(singleRowResult[12].toString())){
                // 触碰规则
                if(singleRowResult[9]!=null){
                    obj[12] = singleRowResult[9].toString();
                } else {
                    obj[12] = "";
                }
                //风险值x
                if(singleRowResult[10]!=null){
                    obj[13] = singleRowResult[10].toString();
                } else {
                    obj[13] = "";
                }
                //风险值y
                if(singleRowResult[11]!=null){
                    obj[14] = singleRowResult[11].toString();
                } else {
                    obj[14] = "";
                }
                //未处理方式
                if(singleRowResult[8]!=null){
                    obj[15] = getDealType(singleRowResult[8].toString());
                } else {
                    obj[15] = "";
                }
            }else{
                // 处理方式
                if(singleRowResult[8]!=null){
                    obj[8] = getDealType(singleRowResult[8].toString());
                } else {
                    obj[8] = "";
                }
                //触发规则
                if(singleRowResult[9]!=null){
                    obj[9] = singleRowResult[9].toString();
                } else {
                    obj[9] = "";
                }
                //风险值x
                if(singleRowResult[10]!=null){
                    obj[10] = singleRowResult[10].toString();
                } else {
                    obj[10] = "";
                }
                //风险值y
                if(singleRowResult[11]!=null){
                    obj[11] = singleRowResult[11].toString();
                } else {
                    obj[11] = "";
                }
            }
            result.add(obj);
        }
        return result;
    }

    /**
     * 工作日T+0额度管理查询
     */
    private List<Object[]> format115Data(List<Object[]> dataList){
        List<Object[]> result = new ArrayList<>();
        for (Object[] singleRowResult : dataList) {
            Object[] obj = new Object[4];
            //custId
            obj[0] = singleRowResult[0];
            //custName
            obj[1] = singleRowResult[1];
            //custType
            String custType = StringTools.checkNulltoString(singleRowResult[3]);
            if(org.apache.commons.lang3.StringUtils.equals
                    (AutoCustTypeEnum.Person.value,AutoCustTypeEnum.get(custType).value)){
                obj[3] = "个人";
            }else if(org.apache.commons.lang3.StringUtils.equals
                    (AutoCustTypeEnum.Corp.value,AutoCustTypeEnum.get(custType).value)){
                obj[3] = "企业";
            }else{
                //错误的结果
                obj[3] = "-";
            }
            //limitAmt
            if(singleRowResult[2]!=null){
                obj[2] = ((BigDecimal)singleRowResult[2]).setScale(2, RoundingMode.HALF_UP).toString();
            }else{
                if(custType!=null&& org.apache.commons.lang3.StringUtils.equals
                        (AutoCustTypeEnum.Person.value,AutoCustTypeEnum.get(custType).value)){
                    obj[2] = (DefaultAutoSettleLimitEnum.DefaultPersonWorkDayLimit.value.
                            setScale(2, RoundingMode.HALF_UP).toString());
                }else if(custType!=null&& org.apache.commons.lang3.StringUtils.equals
                        (AutoCustTypeEnum.Corp.value,AutoCustTypeEnum.get(custType).value)){
                    obj[2] = (DefaultAutoSettleLimitEnum.DefaultCorpWorkDayLimit.value.
                            setScale(2, RoundingMode.HALF_UP).toString());
                }else{
                    //错误的结果
                    obj[2] = "-";
                }
            }


            result.add(obj);
        }
        return result;
    }

    /**
     * 活动用户查询
     * @param dataList
     * @return
     */
    private List<Object[]> format112Data(List<Object[]> dataList) {
        List<Object[]> result = new ArrayList<>();
        //int rowNum = 1;
        DateFormat dateFormat = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss", Locale.CHINESE);
        for (Object[] singleRowResult : dataList) {
            Object[] obj = new Object[14];
            /*// 序号
            obj[0] = rowNum + "";
            rowNum ++;*/
            // 注册时间
            if(singleRowResult[0]!=null){
                obj[1] = dateFormat.format((Timestamp) singleRowResult[0]);
            } else {
                obj[1] = "";
            }
            // 邀请时间
            if(singleRowResult[1]!=null){
                obj[2] = dateFormat.format((Timestamp) singleRowResult[1]);
            } else {
                obj[2] = "";
            }
            // 受邀人名称
            obj[3] = StringTools.checkNulltoString(singleRowResult[2]);
            // 受邀人手机号
            obj[4] = StringTools.checkNulltoString(singleRowResult[3]);
            // 受邀人所在地
            if(!"".equals(StringTools.checkNulltoString(singleRowResult[4]))){
                obj[5] = StringTools.checkNulltoString(singleRowResult[4]);
            }else{
                obj[5] = "未知";
            }
            // 受邀人IP地址
            if(!"".equals(StringTools.checkNulltoString(singleRowResult[5]))){
                obj[6] = StringTools.checkNulltoString(singleRowResult[5]);
            }else{
                obj[6] = "未知";
            }
            // 邀请人名称
            obj[7] = StringTools.checkNulltoString(singleRowResult[6]);
            // 邀请人手机号
            obj[8] = StringTools.checkNulltoString(singleRowResult[7]);
            // 邀请人所在地
            if(!"".equals(StringTools.checkNulltoString(singleRowResult[8]))){
                obj[9] = StringTools.checkNulltoString(singleRowResult[8]);
            }else{
                obj[9] = "未知";
            }
            // 注册状态
            if("".equals(StringTools.checkNulltoString(singleRowResult[10]))){
                obj[10] = "--";
            }else{
                obj[10] = "1".equals(StringTools.checkNulltoString(singleRowResult[10])) ? "已注册" : "未注册";
            }
            // 邀请状态
            if("".equals(StringTools.checkNulltoString(singleRowResult[11]))){
                obj[11] = "--";
            }else{
                obj[11] = "1".equals(StringTools.checkNulltoString(singleRowResult[11])) ? "邀请成功" : "邀请失败";
            }
            // 实名状态
            if(CustPersonInfoConstants.REAL_NAME_CERTIFY_YES.equals(StringTools.checkNulltoString(singleRowResult[12]))){
                obj[12] = "已实名已认证";
            }else if (CustPersonInfoConstants.REAL_NAME_CERTIFY_HALF.equals(StringTools.checkNulltoString(singleRowResult[12]))){
                obj[12] = "已实名未认证";
            }else if(CustPersonInfoConstants.REAL_NAME_CERTIFY_NO.equals(StringTools.checkNulltoString(singleRowResult[12]))){
                obj[12] = "未实名未认证";
            }else{
                obj[12] = "--";
            }
            // 备注
            obj[13] = StringTools.checkNulltoString(singleRowResult[13]);

            result.add(obj);
        }
        return result;
    }

    /**
     * 自动对账结果查询
     * @param dataList
     * @return
     */
    /**
     * 自动对账结果查询
     * @param dataList
     * @return
     */
    private List<Object[]> format113Data(List<Object[]> dataList) {
        List<Object[]> result = new ArrayList<>();
        DateFormat dateFormat = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss", Locale.CHINESE);
        for (Object[] singleRowResult : dataList) {
            Object[] obj = new Object[15];

            // 完成对账时间
            if(singleRowResult[0]!=null){
                obj[0] =  singleRowResult[0];
            } else {
                obj[0] = "";
            }
            // 申请时间
            if(singleRowResult[1]!=null){
                obj[1] =  singleRowResult[1];
            } else {
                obj[1] = "";
            }

            // 自动付款时间
            if(singleRowResult[2]!=null){
                obj[2] =  singleRowResult[2];
            } else {
                obj[2] = "";
            }

            // 结算时间
            if(singleRowResult[3]!=null){
                obj[3] =  singleRowResult[3];
            } else {
                obj[3] = "";
            }

            // 银行交易时间
            if(singleRowResult[14]!=null){
                obj[4] = singleRowResult[14];
            } else {
                obj[4] = "";
            }

            // 国付宝交易号
            obj[5] = StringTools.checkNulltoString(singleRowResult[4]);

            // 银行渠道
            obj[6] = StringTools.checkNulltoString(singleRowResult[5]);
            // 国付宝金额
            obj[7] = StringTools.checkNulltoString(singleRowResult[6]);

            // 交易状态
            if(singleRowResult[15]!=null){
                obj[8] = TxnStaCode.getCategory(Integer.parseInt(singleRowResult[15].toString()));
            } else {
                obj[8] = "";
            }

            // 付款状态
            if(singleRowResult[7]!=null){
                obj[9] = PayStat.translate(singleRowResult[7].toString());
            } else {
                obj[9] = "";
            }

            // 银行金额
            obj[10] = StringTools.checkNulltoString(singleRowResult[8]);

            // 银行状态
            if(singleRowResult[9]!=null){
                obj[11] = AutoSettleCheckOrderConstants.getCoBankStatus(singleRowResult[9].toString());
            }else{
                obj[11] = "";
            }

            //对账状态
            if(singleRowResult[10]!=null){
                obj[12] = AutoSettleCheckOrderConstants.getCoStatus(singleRowResult[10].toString());
            }else{
                obj[12] = "";
            }

            //对账状态细分
            if(singleRowResult[11]!=null){
                obj[13] = AutoSettleCheckOrderConstants.getCoDetail(singleRowResult[11].toString());
            }else{
                obj[13] = "";
            }

            // 备注
            obj[14] = StringTools.checkNulltoString(singleRowResult[12]);

            result.add(obj);
        }
        return result;
    }

    /**
     * 安全日志查询
     * @param dataList
     * @return
     */
    private List<Object[]> format110Data(List<Object[]> dataList) {

        //int rowNum = 1;
        for(Object[] obj : dataList) {
            /*String num = ""+rowNum;
            if(rowNum<10)  num = "0"+ rowNum;
            rowNum++;
            // 序号
            obj[0] = num;*/
            // 系统平台
            obj[1] = operLogTranslateHelper.getLogPlatformCdMap().get(obj[1]);
            // 目录
            obj[2] = operLogTranslateHelper.getLogCategoryCdMap().get(obj[2]);
            // 模块
            obj[3] = operLogTranslateHelper.getLogModulesCdMap().get(obj[3]);
            // 客户端IP
            obj[10] = StringTools.checkNulltoString(obj[10]);
            // 服务端IP
            obj[9] = StringTools.checkNulltoString(obj[9]);
            // 操作时间
            obj[8] = DateUtils.format((Date) obj[8],"yyyy-MM-dd HH:mm:ss");
            // 状态
            obj[6] = getOperStatusName((String) obj[6]);
            // 线程ID
            obj[7] = StringTools.checkNulltoString(obj[7]);
            // 发起方ID
            obj[11] = StringTools.checkNulltoString(obj[11]);
            // 发起方名称
            obj[12] = StringTools.checkNulltoString(obj[12]);
            // 操作详情
            obj[5] = StringTools.checkNulltoString(obj[5]);
            // 备注
            obj[21] = StringTools.checkNulltoString(obj[21]);
            obj[24] = null == obj[24] ?
                    "" : (PwdCtrlFingerTypeConstants.PD_REG.equals(obj[24]) ? "创建用户模型" : "认证服务");
            obj[25] = StringTools.checkNulltoString(obj[25]);
        }
        return dataList;
    }

    /**
     * 人工审核交易查询
     * @param dataList
     * @return
     */
    private List<Object[]> format109Data(List<Object[]> dataList) {
        List<Object[]> result = new ArrayList<>();
        for (Object[] o : dataList) {
            Object[] obj = new Object[17];
            /*// 序号
            obj[0] = StringTools.checkNulltoString(o[21]);*/
            // 商户订单号
            obj[1] = StringTools.checkNulltoString(o[0]);
            // 国付宝交易号
            obj[2] = StringTools.checkNulltoString(o[1]);
            // 国付宝交易时间
            obj[3] = StringTools.checkNulltoString(o[2]);
            // 付款客户名称
            obj[4] = SensitiveInfoUtils.smartDecryptWithCheck(StringTools.checkNulltoString(o[3]));
            // 付款客户手机号
            obj[5] = SensitiveInfoUtils.smartDecryptWithCheck(StringTools.checkNulltoString(o[4]));
            // 付款银行卡号
            obj[6] = showLastStrNum(SensitiveInfoUtils.smartDecryptWithCheck(o[5].toString()),4);
            // 发卡行
            obj[7] = StringTools.checkNulltoString(o[6]);
            // 交易金额(元)
            obj[8] = StringTools.checkNulltoString(o[7]);
            // 交易场所
            obj[9] = StringTools.checkNulltoString(o[8]);
            // 交易类型
            obj[10] = IntTxnCd.getIntTxnCdName(o[9].toString());
            // 支付渠道
            obj[11] = DicTranslate.getPayChannelName(o[10].toString());
            // 触发规则
            obj[12] = StringTools.checkNulltoString(o[11]);
            // 处置状态
            obj[13] = StringTools.checkNulltoString(o[12]);
            // 是否为本人交易
            if(o[13] != null){
                if(RiskControlConstants.IS_SELF.equals(o[13].toString())){
                    obj[14] = "是";
                }else{
                    obj[14] = "否";
                }
            }
            // 确认信息
            obj[15] = StringTools.checkNulltoString(o[18]);
            // 处置方案
            String dealBanksolu = "";
            if(o[14] !=null){
                if(RiskControlConstants.DEAL_TYPE_ENABLED.equals(o[14].toString())){
                    dealBanksolu = "银行卡加入黑名单";
                }
            }
            String dealCustsolu = null;
            if(o[15] !=null){
                if(RiskControlConstants.DEAL_TYPE_ENABLED.equals(o[15].toString())){
                    dealCustsolu = "付款客户锁定";
                }
            }
            String auditReason = null;
            if(StringUtils.isNotBlank(dealBanksolu)){
                auditReason=dealBanksolu+";";
            }
            if(StringUtils.isNotBlank(dealCustsolu)){
                auditReason=auditReason+dealCustsolu;
            }
            obj[16] = auditReason;

            result.add(obj);
        }
        return result;
    }

    /**
     * 可疑交易管理
     * @param dataList
     * @return
     */
    private List<Object[]> format108Data(List<Object[]> dataList) {
        String dateFormat = "yyyy-MM-dd HH:mm:ss";
        List<Object[]> result = new ArrayList<>();
        for (Object[] arr : dataList) {
            Object[] obj = new Object[14];
            // 触发时间
            if (arr[1] != null) {
                obj[0] = DateUtils.format((Date) arr[1], dateFormat);
            } else {
                obj[0] = "";
            }
            // 交易时间
            obj[1] = StringTools.checkNulltoString(arr[12]);
            // 交易订单号
            obj[2] = StringTools.checkNulltoString(arr[4]);
            // 客户名称
            obj[3] = StringTools.checkNulltoString(arr[13]);
            // 客户类型
            if (arr[14] != null) {
                if ("1".equals(arr[14].toString())) {
                    obj[4] = "个人";
                } else {
                    obj[4] = "企业";
                }
            }
            // 交易类型
            obj[5] = DicTranslate.getIntTxnCdStr(StringTools.checkNulltoString(arr[5]));
            // 支付方式
            obj[6] = DicTranslate.getPayChannelName(StringTools.checkNulltoString(arr[6]));
            // 交易金额
            obj[7] = StringTools.checkNulltoString(arr[15]);
            // 命中阈值
            if (arr[4] != null && !"".equals(arr[4])) { //excel导出命中的阈值
                if(RiskControlConstants.DEAL_TYPE_THROUGH_AND_SUSPICIOUS.equals(arr[7].toString())){
                    obj[8] = hitThreshold((String) arr[4]);
                }else{
                    obj[8] = "";
                }

            }
            // 处理方式
            obj[9] = getDealType(arr[7].toString());
            // 最后处理日期
            if(arr[8] != null) {
                obj[10] = DateUtils.format((Date) arr[8], dateFormat);
            } else {
                obj[10] = "";
            }
            // 最后处理用户
            obj[11] = StringTools.checkNulltoString(arr[9]);
            // 处理状态
            obj[12] = getDealStat(StringTools.checkNulltoString(arr[10]));
            //触发规则
            obj[13] = StringTools.checkNulltoString(arr[16]);

            result.add(obj);
        }

        return result;
    }

    /**
     * 客户信息查询
     * @param dataList
     * @return
     */
    private List<Object[]> format107Data(List<Object[]> dataList) {
        List<Object[]> result = new ArrayList<>();
        for (Object[] info : dataList) {
            Object[] obj = new Object[14];
            // 客户ID
            obj[0] = StringTools.checkNulltoString(info[0]);
            // 注册日期
            if(info[1] != null) {
                obj[1] = DateUtils.formatDate((Date) info[1], DateUtils.yyyy_MM_dd_HH_mm_ss);
            } else {
                obj[1] = "";
            }
            // 客户名称
            obj[2] = StringTools.checkNulltoString(info[2]);
            // Email
            obj[3] = StringTools.checkNulltoString(info[3]);
            // 手机号码
            if(info[4] != null) {
                obj[4] = SensitiveInfoUtils.smartDecryptWithCheck(info[4].toString());
            } else {
                obj[4] = "";
            }
            // 账号
            obj[5] = StringTools.checkNulltoString(info[5]);
            // 账户类型
            if (info[11] == null) {
                obj[6] = "";
            } else {
                obj[6] = DicTranslate.getAcctProd(info[11].toString());
            }
            // 客户类型
            obj[7] = "2".equals(StringTools.checkNulltoString(info[7])) ? "企业" : "个人";
            // 客户状态
            obj[8] = DicTranslate.getCustStat(info[8].toString(),info[7].toString());
            // 实名认证状态
            obj[9] = getIsRealNameCertify(StringTools.checkNulltoString(info[9]));
            // 实时余额
            if(info[10] != null) {
                obj[10] = ((BigDecimal) info[10]).setScale(2, RoundingMode.HALF_UP).toString();
            } else {
                obj[10] = new BigDecimal(0).setScale(2).toString();
            }
            // 日终余额
            if(info[18] != null) {
                obj[11] = ((BigDecimal) info[18]).setScale(2, RoundingMode.HALF_UP).toString();
            } else {
                obj[11] = new BigDecimal(0).setScale(2).toString();
            }
            // 这两个字段没法通过异步导出的方式来做

            // 开通业务
            // 结算周期

            result.add(obj);
        }

        return result;
    }

    private String getIsRealNameCertify(String s) {
        String value = "";
        if("0".equals(s)) {
            value = "未实名未认证";
        } else if("1".equals(s)) {
            value = "已实名已认证";
        } else if("2".equals(s)) {
            value = "已实名未认证";
        }
        return value;
    }

    /**
     * 余额查询
     * @param dataList
     * @return
     */
    private List<Object[]> format106Data(List<Object[]> dataList) {
        List<Object[]> result = new ArrayList<>();
        for (Object[] arr : dataList) {
            Object[] obj = new Object[13];
            // 客户名称/账户名称
            obj[0] = StringTools.checkNulltoString(arr[3]);
            // 用户名
            obj[1] = getCustEmailAndMobile(StringTools.checkNulltoString(arr[4]),SensitiveInfoUtils.smartDecryptWithCheck(StringTools.checkNulltoString(arr[5])));
            // 国付宝账户
            obj[2] = StringTools.checkNulltoString(arr[0]);
            // 账户类型
            obj[3] = DicTranslate.getAcctProd(StringTools.checkNulltoString(arr[1]));
            // 账户余额(元)
            if(arr[6] != null) {
                obj[4] = ((BigDecimal) arr[6]).setScale(2, RoundingMode.HALF_UP).toString();
            } else {
                obj[4] = new BigDecimal(0).setScale(2).toString();
            }
            // 当前冻结金额(元)
            if(arr[7] != null) {
                obj[5] = ((BigDecimal) arr[7]).setScale(2, RoundingMode.HALF_UP).toString();
            } else {
                obj[5] = new BigDecimal(0).setScale(2).toString();
            }
            result.add(obj);
        }
        return result;
    }

    /**
     * 线下付款查询
     * @param dataList
     * @return
     */
    private List<Object[]> format105Data(List<Object[]> dataList) {
        List<Object[]> result = new ArrayList<>();
        for(Object[] obj : dataList) {
            Object[] o = new Object[8];
            // 交易日期
            o[0] = StringTools.checkNulltoString(obj[2]);
            // 国付宝交易号
            o[1] = StringTools.checkNulltoString(obj[4]);
            // 充值金额
            if(obj[5] != null) {
                o[2] = ((BigDecimal) obj[5]).setScale(2).toString();
            } else {
                o[2] = new BigDecimal(0).setScale(2).toString();
            }
            // 充值银行
            o[3] = StringTools.checkNulltoString(obj[7]);
            // 客户名称
            o[4] = StringTools.checkNulltoString(obj[8]);
            // Email
            o[5] = StringTools.checkNulltoString(obj[9]);
            // 审核状态
            if (StringUtils.isBlank(StringTools.checkNulltoString(obj[0]))) {
                // 此名称在审批流程approvalAll()方法中有引用，修改时需要同步修改
                o[6] = "待初审";
            } else {
                int stepNum = Integer.parseInt(obj[0].toString());
                int decision = Integer.parseInt(obj[1].toString());
                if (stepNum == 1 && decision == 2) {
                    o[6] = "初审拒绝";
                } else if (stepNum == 1 && decision == 1) {
                    o[6] = "待终审";
                } else if (stepNum == 2 && decision == 2) {
                    o[6] = "终审拒绝";
                } else if (stepNum == 2 && decision == 1) {
                    o[6] = "终审完成";
                }
            }
            // 交易状态
            o[7] = TxnStaCode.getCategory(NumberUtils.toInt(StringTools
                    .checkNulltoString(obj[6])));

            result.add(o);
        }

        return result;
    }

    /**
     * 付款退款结果查询
     * @param dataList
     * @return
     */
    private List<Object[]> format104Data(List<Object[]> dataList) {
        for (Object[] tranInfo : dataList) {

            // 退款申请时间
            tranInfo[0] = DateUtils.formatDate((Date) tranInfo[0], DateUtils.yyyy_MM_dd_HH_mm_ss);
            // 退款交易号
            tranInfo[1] = StringTools.checkNulltoString(tranInfo[1]);
            // 银行退款订单号
            tranInfo[2] = StringTools.checkNulltoString(tranInfo[2]);
            // 收款方Email
            tranInfo[3] = StringTools.checkNulltoString(tranInfo[3]);
            // 收款方名称
            tranInfo[4] = StringTools.checkNulltoString(tranInfo[4]);
            // 国付宝账号
            tranInfo[5] = StringTools.checkNulltoString(tranInfo[5]);
            // 退款金额
            tranInfo[6] = MoneyUtil.moneyFormat(tranInfo[6]==null?"0":tranInfo[6].toString());
            // 退款状态
            String stat = StringTools.checkNulltoString(tranInfo[7]);
            tranInfo[7] = "";
            if (stat.equals("")) {
                tranInfo[7] = DicTranslate.getTxnStaCdName(0);
            } else {
                if (stat.equals(TxnStaCode.TXN_STA_CD_20000.value+"")) {
                    tranInfo[7] = "退款成功";
                } else {
                    // 审核结果 1通过 2拒绝
                    String decision = StringTools.checkNulltoString(tranInfo[10]);
                    // 审核步骤
                    String step_num = StringTools.checkNulltoString(tranInfo[11]);
                    if (step_num.equals("")) {
                        tranInfo[7] = "待初审";
                    } else if (decision.equals("2")) {
                        tranInfo[7] = "审核拒绝";
                    } else if (step_num.equals("1") && decision.equals("1")) {
                        tranInfo[7] = "待确认";
                    }
                }
            }
            //付款渠道
            tranInfo[8] = StringTools.checkNulltoString(tranInfo[8]);
            // 交易状态
            if (StringTools.checkNulltoString(stat).equals("")) {
                tranInfo[9] = TxnStaCode.getCategory(0);
            } else {
                tranInfo[9] = TxnStaCode.getCategory(Integer.parseInt(StringTools.checkNulltoString(stat)));
            }

        }
        return dataList;
    }

    /**
     * 付款查询
     * @param dataList
     * @return
     */
    private List<Object[]> format103Data(List<Object[]> dataList) {
        for (Object[] tranInfo : dataList) {
            //申请时间
            if(tranInfo[0] != null) {
                tranInfo[0] = DateUtils.formatDate((Date) tranInfo[0], DateUtils.yyyy_MM_dd_HH_mm_ss);
            } else {
                tranInfo[0] = "";
            }
            // 付款确认时间
            if(tranInfo[1] != null) {
                tranInfo[1] = DateUtils.formatDate((Date) tranInfo[1], DateUtils.yyyy_MM_dd_HH_mm_ss);
            } else {
                tranInfo[1] = "";
            }
            // 国付宝交易号
            tranInfo[2] = StringTools.checkNulltoString(tranInfo[2]);
            // 付款方
            tranInfo[4] = StringTools.checkNulltoString(tranInfo[4]);
            // 客户级别
            tranInfo[5] = DicTranslate.getCustLevel(StringTools.checkNulltoString(tranInfo[5]));
            // 国付宝账号
            tranInfo[6] = StringTools.checkNulltoString(tranInfo[6]);
            // 账户类型
            tranInfo[26] = StringTools.checkNulltoString(tranInfo[26]);
            // 付款银行
            tranInfo[28] = StringTools.checkNulltoString(tranInfo[28]);
            //银行付款订单号
            tranInfo[7] = StringTools.checkNulltoString(tranInfo[7]);
            //收款方
            tranInfo[9] = SensitiveInfoUtils.smartDecryptWithCheck(StringTools.checkNulltoString(tranInfo[9]));
            //开户银行
            tranInfo[10] = StringTools.checkNulltoString(tranInfo[10]);
            //网点名称
            tranInfo[12] = StringTools.checkNulltoString(tranInfo[12]);
            //收款方银行账号
            tranInfo[13] = SensitiveInfoUtils.smartDecryptWithCheck(StringTools.checkNulltoString(tranInfo[13]));
            //对公/私
            tranInfo[14] = DicTranslate.getBCFlag(StringTools.checkNulltoString(tranInfo[14]));
            //交易金额
            tranInfo[15] = MoneyUtil.moneyFormat(tranInfo[15] == null ? "0" : tranInfo[15].toString(), 2);
            //交易类别
            tranInfo[16] = IntTxnCd.getIntTxnCdName(StringTools.checkNulltoString(tranInfo[16]));
            //批次号
            tranInfo[17] = StringTools.checkNulltoString(tranInfo[17]);
            //包号
            tranInfo[18] = StringTools.checkNulltoString(tranInfo[18]);
            //结算周期
            tranInfo[19] = (tranInfo[19] == null) ? "" : "T+" + StringTools.checkNulltoString(tranInfo[19]);
            //付款状态
            String isRefund = StringTools.checkNulltoString(tranInfo[24]);
            String payStatus = "";
            if (isRefund.equals("1")) {
                payStatus = "发起退款";
            } else if (isRefund.equals("2")) {
                payStatus = "发起退款"; //退款成功
            } else if (isRefund.equals("3")) {
                payStatus = "付款成功"; //退款失败
            }//先判断自动结算订单的付款状态
            else if ("线上（自动）".equals(StringTools.checkNulltoString(tranInfo[21])) && StringTools.checkNulltoString(tranInfo[31]).equals(PayStat._01.value)
                    && !StringTools.checkNulltoString(tranInfo[29]).equals("1") && !StringTools.checkNulltoString(tranInfo[30]).equals("2")) {
                payStatus = "待确认付款";
            } else if ("线上（自动）".equals(StringTools.checkNulltoString(tranInfo[21])) && StringTools.checkNulltoString(tranInfo[31]).equals(PayStat._02.value)) {
                payStatus = "付款进行中";
            } else if ("线上（自动）".equals(StringTools.checkNulltoString(tranInfo[21])) && StringTools.checkNulltoString(tranInfo[31]).equals(PayStat._04.value)) {
                payStatus = "付款成功";
            } else if ("线上（自动）".equals(StringTools.checkNulltoString(tranInfo[21])) && StringTools.checkNulltoString(tranInfo[31]).equals(PayStat._05.value)
                    && !StringTools.checkNulltoString(tranInfo[29]).equals("1") && !StringTools.checkNulltoString(tranInfo[30]).equals("2")) {
                payStatus = "付款失败";
            } else if ("线上（自动）".equals(StringTools.checkNulltoString(tranInfo[21])) && StringTools.checkNulltoString(tranInfo[31]).equals(PayStat._07.value)) {
                payStatus = "付款申请已受理";
            }
            //非自动结算付款状态
            else if (StringTools.checkNulltoString(tranInfo[20]).equals(TxnStaCode.TXN_STA_CD_30101.value + "")) {
                //payStatus = "外部清算机构处理中";
                payStatus = "付款进行中";
            } else if (StringTools.checkNulltoString(tranInfo[29]).equals("0")) {
                payStatus = "待审核";
            } else if (StringTools.checkNulltoString(tranInfo[29]).equals("1") && StringTools.checkNulltoString(tranInfo[30]).equals("2")) {
                payStatus = "审核拒绝";
            } else if (StringTools.checkNulltoString(tranInfo[29]).equals("1") && StringTools.checkNulltoString(tranInfo[30]).equals("1")) {
                payStatus = "待确认付款";
            } else if (StringTools.checkNulltoString(tranInfo[29]).equals("2") && StringTools.checkNulltoString(tranInfo[30]).equals("1")) {
                payStatus = "付款成功";
            } else if (StringTools.checkNulltoString(tranInfo[29]).equals("2") && StringTools.checkNulltoString(tranInfo[30]).equals("2")) {
                payStatus = "付款失败";
            }
            tranInfo[29] = payStatus;
            // 交易状态
            if (StringTools.checkNulltoString(tranInfo[20]).equals("")) {
                tranInfo[20] = TxnStaCode.getCategory(0);
            } else {
                tranInfo[20] = TxnStaCode.getCategory(Integer.parseInt(StringTools.checkNulltoString(tranInfo[20])));
            }
            //付款渠道
            tranInfo[21] = StringTools.checkNulltoString(tranInfo[21]);
            //失败原因
            tranInfo[34] = StringTools.checkNulltoString(tranInfo[34]);
            //转手动结算原因
            tranInfo[37] = StringTools.checkNulltoString(tranInfo[37]);
            tranInfo[43] = StringTools.checkNulltoString(AcctType.translate(StringTools.checkNulltoString(tranInfo[43])));
            tranInfo[44] = StringTools.checkNulltoString(tranInfo[44]);
        }
        return dataList;
    }

    /**
     * 对结算结果查询结果集特殊处理
     * @param dataList
     */
    private List<Object[]> format102Data(List<Object[]> dataList) {

        String dateFormat = "yyyy-MM-dd HH:mm:ss";

        for (Object[] arr : dataList) {
            if(arr[0]!=null){
                arr[0] = DateUtils.format((Date) arr[0], dateFormat);
            }else{
                arr[0] = "";
            } //申请时间

            if(arr[1]!=null){
                arr[1] = DateUtils.format((Date) arr[1], dateFormat);
            } //自动付款时间

            arr[3] = IntTxnCd.getIntTxnCdName((String) arr[3]); //交易类型


            if(arr[5]!=null){
                arr[5] = ((BigDecimal)arr[5]).setScale(2, RoundingMode.HALF_UP).toString();
            } //交易金额

            //订单状态
            int txnSts=((BigDecimal)arr[6]).intValue();
            arr[6] = TxnStaCode.getCategory(txnSts);

            // 收款方
            arr[7] = SensitiveInfoUtils.smartDecryptWithCheck((String) arr[7]);

            // 收款方银行账号
            arr[8] = SensitiveInfoUtils.smartDecryptWithCheck((String) arr[8]);

            if(arr[13]!=null){
                arr[13] = ((BigDecimal)arr[13]).setScale(2, RoundingMode.HALF_UP).toString();
            } //结算限额

            arr[14] = (arr[14]==null||"1".equals(((Character)arr[14]).toString()))?"是":"否"; // 是否紧急
            arr[15] = DicTranslate.getPayBankName((String) arr[15]);

            if(arr[24]!=null){
                arr[24] = PayStat.translate((String) arr[24]);
            } //付款状态

            if(arr[27]!=null){
                arr[27] = OnlineRelaunch.translate((String) arr[27]);
            } //可否重新发起

            if(arr[29]==null){
                arr[29] = "";
            }else{
                arr[29] = "T+"+((BigDecimal) arr[29]).toString();
            } //结算周期
            if(arr[30]==null){
                arr[30] = "";
            }else{
                String stlmEX ="";

                switch ((String)arr[30]) {
                    case SettlementExchangeConstants.STLM_EX_ICBC_CROSS:
                        stlmEX = "工行大额";
                        break;
                    case SettlementExchangeConstants.STLM_EX_ICBC_SUPER:
                        stlmEX = "工行超级网银";
                        break;
                    case SettlementExchangeConstants.STLM_EX_ICBC:
                        stlmEX = "中国工商银行";
                        break;
                    case SettlementExchangeConstants.STLM_EX_ABC:
                        stlmEX = "中国农业银行";
                        break;
                    //招商银行对公显示为招商银行，招商银行对私走超级网银
                    case SettlementExchangeConstants.STLM_EX_CMB_C:
                        stlmEX = "招商银行_对公";
                        break;
                    case SettlementExchangeConstants.STLM_EX_CMB_P:
                        stlmEX = "招商银行_对私";
                        break;
                    case SettlementExchangeConstants.STLM_EX_BOC:
                        stlmEX = "中国银行";
                        break;
                    case SettlementExchangeConstants.STLM_EX_CCB:
                        stlmEX = "中国建设银行";
                        break;
                    default:
                        stlmEX = (String)arr[30];
                        break;
                }

                arr[30] = stlmEX;
            } //结算汇路

            if(arr[33]!=null){
                DateFormat df = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss", Locale.CHINESE);
                String autoSettleTime = df.format((Timestamp) arr[33]);
                arr[33] = autoSettleTime;
            } //结算时间

            //add by tianxin 2018年2月23日
            //翻译银行订单状态
            if (arr[39]!=null) {
                arr[39] = BankOrderTranslateUtil.translateBankOrderStatus((String) arr[39]);
            }

        }
        return dataList;
    }

    /**
     * 对收款退款查询结果集特殊处理
     * @param dataList
     */
    private List<Object[]> format101Data(List<Object[]> dataList) {
        List<Object[]> list = new ArrayList<>();
        for(Object[] obj : dataList){
            Object[] vo = new Object[16];
            vo[0] = StringTools.checkNulltoString(obj[2]); //交易时间
            vo[1] = StringTools.checkNulltoString(obj[3]); //退款确认时间
            vo[2] = StringTools.checkNulltoString(obj[4]); //客户订单号
            vo[3] = StringTools.checkNulltoString(obj[1]); //国付宝交易号
            vo[4] = StringTools.checkNulltoString(obj[5]); //银行订单号
            vo[5] = StringTools.checkNulltoString(obj[6]); //原订单金额
            vo[6] = StringTools.checkNulltoString(obj[7]); //累计退款金额
            vo[7] = StringTools.checkNulltoString(obj[8]); //退款金额
            vo[8] = (StringUtils.isBlank(StringTools.checkNulltoString(obj[10])) || !StringTools.checkNulltoString(obj[10]).equals("3")) ? "否" : "是";//强制退款
            vo[9] = StringTools.checkNulltoString(obj[9]); //付款银行
            vo[10] = StringTools.checkNulltoString(obj[11]); //客户名称
            vo[11] = DicTranslate.getIntTxnCdStr(StringTools.checkNulltoString(obj[14])); //类别
            vo[12] = StringTools.checkNulltoString(obj[13]); //国付宝账号
            vo[13] = this.getRefundState(StringTools.checkNulltoString(obj[15]), StringTools.checkNulltoString(obj[16]),StringTools.checkNulltoString(obj[18]),StringTools.checkNulltoString(obj[17]));//退款状态
            vo[14] = TxnStaCode.getCategory(Integer.parseInt(StringTools.checkNulltoString(obj[17])));//交易状态
            vo[15] = StringTools.checkNulltoString(obj[12]); //包号
            list.add(vo);
        }
        return list;
    }

    /**
     * 对交易查询结果集特殊处理
     * @param dataList
     */
    private List<Object[]> format100Date(List<Object[]> dataList) {
        //交易查询结果特殊处理
        for (Object[] obj:dataList) {
            if("00500".equals(obj[19])){
                if(("01".equals(obj[23])))
                {
                    if("CLOUD".equals(obj[18]) || "SUBCLOUD".equals(obj[18])){
                        String gopayOrderId = (String)obj[3];
                        String sql ="select mer_id,gopay_order_id from cps_gen_main_order where gopay_order_id = :gopayOrderId";
                        Map<String,Object> param = new HashMap<String,Object>();
                        param.put("gopayOrderId",gopayOrderId);
                        List<Object[]> list = commonQueryDAO.findBySql(sql,param);
                        if(!list.isEmpty() && null != list.get(0)[0]){
                            String merId = list.get(0)[0].toString();
                            if("GFB0000001".equals(merId)){
                                obj[4]=obj[5];
                            }else{
                                String custSql = "select corp_name,cust_id from cust_corp_info where cust_id = :custId";
                                Map<String,Object> paramCust = new HashMap<String,Object>();
                                paramCust.put("custId",merId);
                                List<Object[]> custList = commonQueryDAO.findBySql(custSql,paramCust);
                                if(custList.size() !=0){
                                    String custName = custList.get(0)[0].toString();
                                    obj[4]=custName;
                                }
                            }
                        }
                    }else if("03".equals(obj[22]) || "02".equals(obj[22]) || Integer.parseInt(String.valueOf(obj[21])) == 20001 || obj[22] == null || obj[22] == " "){
                        obj[4] = "国付宝";
                    }
                }
                if(("08".equals(obj[23])))
                {obj[4] = "生钱包";    }
                if(("05".equals(obj[23])))
                {obj[4] = "国付宝钱包";    }
            }
            if("01615".equals(obj[19])){
                if(("05".equals(obj[23])))
                {obj[4] = "国付宝钱包";    }
            }

            obj[0] = DateUtils.format((Date)obj[0], "yyyy-MM-dd HH:mm:ss");
            if(obj[1]!=null){
                obj[1] = DateUtils.format((Date) obj[1], "yyyy-MM-dd HH:mm:ss");
            }

            //退款转账手续费应当为负数 add by zhangyu
            if(obj[19].equals(IntTxnCd._00701.value)){
                obj[8] = new BigDecimal("-"+obj[8]);//REAL_FEE_AMT
            }else {
                obj[8] = ((BigDecimal)obj[8]).subtract(obj[28] == null ? new BigDecimal(0) : (BigDecimal) obj[28]);//实收手续费=实收手续费-退款手续费
            }

            obj[19] = DicTranslate.getIntTxnCdStr((String)obj[19]);//GOPAY_INT_TXN_CD
            obj[25] = translateHandleSts((String)obj[25],Integer.parseInt(String.valueOf(obj[21])));//HANDLE_STA_CD
            obj[21] = TxnStaCode.getCategory(Integer.parseInt(String.valueOf(obj[21])));//TXN_STA_CD
            obj[20] = DicTranslate.getPayChannelName((String)obj[20]);//PAY_CHANNEL
            obj[23] = translateTerminalSource((String)obj[23]);//TERMINAL_SOURCE
            obj[22] = translateSettleType((String)obj[22]);//SETTLE_TYPE

            obj[18] = DicTranslate.getAcctProd((String)obj[18]);//PAY_ACCT_PROD_CD

            obj[8] = obj[8] != null ? ((BigDecimal)obj[8]).setScale(2, RoundingMode.HALF_UP).toString():new BigDecimal(0).toString();
            obj[6] = obj[6] == null ?new BigDecimal(0).toString():((BigDecimal)obj[6]).setScale(2, RoundingMode.HALF_UP).toString();//SETTLE_AMT
            obj[7] = obj[7] == null ?new BigDecimal(0).toString():((BigDecimal)obj[7]).setScale(2, RoundingMode.HALF_UP).toString();//TXN_AMT

            String userSourceValue= UserSourceConstans.getValue((String)obj[24]);//USER_SOURCE
            obj[24] = userSourceValue;

            obj[9] = translateOrderFeePayer((String)obj[9]);//ORDER_FEE_PAYER
            obj[27] = DicTranslate.getPayChannelName((String)obj[27]);//OLD_PAY_CHANNEL
        }
        return dataList;
    }

    private String getCustEmailAndMobile(String email,String mobile){
        String h=email;
        if(org.apache.commons.lang3.StringUtils.isNotBlank(mobile)){
            if(org.apache.commons.lang3.StringUtils.isNotBlank(email)){
                h=email+"/"+mobile;
            }else{
                h=mobile;
            }
        }
        return h;
    }

    /**
     * 获取操作状态名称
     *
     * @param operStatusCd
     * @return
     */
    public static String getOperStatusName(String operStatusCd) {
        String operStatusName = "";
        if ("01".equals(operStatusCd)) {
            operStatusName = "成功";
        } else if ("02".equals(operStatusCd)) {
            operStatusName = "失败";
        } else if ("03".equals(operStatusCd)) {
            operStatusName = "回滚";
        } else {
            operStatusName = "未知";
        }
        return operStatusName;
    }

    /**
     * 导出excel中命中的阈值
     *
     * @param gopayOrderId
     */
    private String hitThreshold(String gopayOrderId) {
        Map<String, Object> map = new HashMap<String, Object>();
        StringBuffer sql = new StringBuffer();
        sql.append("select t.type,t.cycle_day,t.cycle_count,to_char(t.cycle_amount, 'FM999999999990.0099') as cycle_amount,");
        sql.append(" to_char(t.single_amount_low, 'FM999999999990.0099') as single_amount_low,");
        sql.append(" to_char(t.single_amount_high, 'FM999999999990.0099') as single_amount_high");
        sql.append("  from rc_order_threshold_snapshot t where t.gopay_order_id =:gopayOrderId");
        sql.append("  order by t.type ");
        map.put("gopayOrderId", gopayOrderId);
        List<Object[]> hitThresholdList = commonQueryDAO.findBySql(sql.toString(), map);
        String hitThreshold = "";
        for (Object[] array : hitThresholdList) {
            if (RiskControlConstants.THRESHOLD_TYPE_CYCLE.equals(array[0].toString())) {
                hitThreshold = hitThreshold + "周期，";
                if (array[1] != null) {
                    hitThreshold = hitThreshold + array[1].toString() + "天";
                }
                if (array[2] != null) {
                    hitThreshold = hitThreshold + "、" + array[2].toString() + "笔";
                }
                if (array[3] != null) {
                    hitThreshold = hitThreshold + "、" + array[3].toString() + "元";
                }
                hitThreshold += "；";
            } else if (RiskControlConstants.THRESHOLD_TYPE_SINGLE.equals(array[0].toString())) {
                hitThreshold = hitThreshold + "单笔，";
                if (array[5] != null) {
                    hitThreshold = hitThreshold + array[5].toString() + "元；";
                }
            }
        }
        return hitThreshold;
    }

    /**
     * 显示后四位数字，其他都用*后显示
     */
    private String showLastStrNum(String org, int length) {
        if (StringUtils.isEmpty(org)) { return org;}
        if (org.length() <= length) { return org;}
        return StringUtils.leftPad("", org.length()-length, '*')+org.substring(org.length()-length,org.length());
    }

    public String getDealStat(String dealStat) {
        String msg = "";
        if ("00".equals(dealStat)|| "".equals(dealStat) || null == dealStat) {
            msg = "未处理";
        } else if ("02".equals(dealStat)) {
            msg = "黑名单";
        } else if ("01".equals(dealStat)) {
            msg = "灰名单";
        } else if ("04".equals(dealStat)) {
            msg = "洗钱上报";
        } else if ("03".equals(dealStat)) {
            msg = "移除可疑交易";
        }else if ("05".equals(dealStat)) {
            msg = "上报反诈骗平台";//上报反诈骗平台  add by jianghoupeng 2016/11/30
        }
        return msg;
    }

    private String translateTerminalSource(String code){
        String name = "";
        if ("01".equals(code)) {
            name = "PC端";
        } else if ("02".equals(code)) {
            name = "移动端";
        }else if("05".equals(code)){
            name = "国付宝钱包";
        }else if("08".equals(code)){
            name = "生钱包";
        }
        return name;
    }

    private String translateSettleType(String code){
        String name = "";
        if ("01".equals(code)) {
            name = "线上（自动）";
        } else if ("02".equals(code)) {
            name = "线上";
        } else if ("03".equals(code)) {
            name = "线下";
        }

        return name;
    }

    private String translateHandleSts(String code,int tranSts){
        String name = "";
        if ("01".equals(code)) {
            name = "处理中";
        } else if ("02".equals(code)) {
            name = "待审核";
        } else if ("03".equals(code)) {
            name = "待确认";
        } else if ("04".equals(code)) {
            name = "成功";
        }else if ("05".equals(code)) {
            name = "失败";
        }
        //转义风控审核中   add by sonfengli 20160823
        if(tranSts== TxnStaCode.TXN_STA_CD_30300.value){
            name = "风控审核中";
        }

        return name;
    }

    private String translateOrderFeePayer(String code) {
        if(code !=null) {
            switch (code) {
                case "00":
                    return "不涉及";
                case "01":
                    return "付方承担";
                case "02":
                    return "收方承担";
                case "03":
                    return "平台承担";
                default:
                    return "";
            }
        }
        return "";
    }

    /**
     * 翻译退款状态
     * @param stepNum
     * @param decision
     * @return
     */
    private String getRefundState(String stepNum,String decision,String payChannel,String txnStaCode){
        if (TxnStaCode.TXN_STA_CD_30101.value == Integer.parseInt(txnStaCode)) {
            return "银行退款处理中";
        }
        //虚拟卡退款
        if(PayChannel._02.value.equals(payChannel) || PayChannel._52.value.equals(payChannel)){
            if (TxnStaCode.TXN_STA_CD_20000.value == Integer.parseInt(txnStaCode)) {
                return "退款成功";
            }else{
                return this.getTranState(txnStaCode);
            }
        }else{
            if(StringUtils.isNotBlank(stepNum)){
                if("1".equals(stepNum)){
                    if(DecisionValue.AGREE.value == Integer.parseInt(decision)){
                        return "待确认";
                    }else{
                        return "审核拒绝";
                    }
                }
                if("2".equals(stepNum)){
                    if(DecisionValue.AGREE.value == Integer.parseInt(decision)){
                        //新增退款状态银行退款失败 add by lilili  20160912
                        if(TxnStaCode.TXN_STA_CD_90101.value == Integer.parseInt(txnStaCode)){
                            return "银行退款失败";
                        }else{
                            return "退款成功";
                        }
                    }else{
                        return "退款失败";
                    }
                }

            }else{
                return "待审核";
            }
        }
        return "";
    }

    /**
     * 处理方式
     * @param dealType
     * @return
     */
    public String getDealType(String dealType) {
        String msg = "";
        if ("01".equals(dealType)) {
            msg = "通过可疑";
        }
        if ("02".equals(dealType)) {
            msg = "阻断";
        }
        if ("03".equals(dealType)) {
            msg = "人工审核";
        }
        if ("09".equals(dealType)) {
            msg = "短信通知";
        }
        if ("10".equals(dealType)) {
            msg = "通过";
        }
        return msg;
    }

    /**
     * 翻译交易状态
     * @param txnCd
     * @return
     */
    private String getTranState(String txnCd) {
        if(StringUtils.isNotBlank(txnCd)){
            if (TxnStaCode.TXN_STA_CD_20000.value == Integer.parseInt(txnCd)) {
                return "成功";
            } else if (TxnStaCode.TXN_STA_CD_30000.value == Integer.parseInt(txnCd) || TxnStaCode.TXN_STA_CD_30101.value == Integer.parseInt(txnCd)
                    || TxnStaCode.TXN_STA_CD_30102.value == Integer.parseInt(txnCd)) {
                return "进行中";
            } else if (TxnStaCode.TXN_STA_CD_20002.value == Integer.parseInt(txnCd) || TxnStaCode.TXN_STA_CD_20003.value == Integer.parseInt(txnCd)
                    || TxnStaCode.TXN_STA_CD_90000.value == Integer.parseInt(txnCd) || TxnStaCode.TXN_STA_CD_90001.value == Integer.parseInt(txnCd)
                    || TxnStaCode.TXN_STA_CD_90101.value == Integer.parseInt(txnCd) || TxnStaCode.TXN_STA_CD_90102.value == Integer.parseInt(txnCd)
                    || TxnStaCode.TXN_STA_CD_90201.value == Integer.parseInt(txnCd)) {
                return "失败";
            }
        }
        return "未知交易状态码" + txnCd;
    }


    @Override
    //@Transactional(propagation = Propagation.REQUIRED, rollbackFor = { Exception.class })
    public void genFile(AsyncExportInfo vo)  {
        // 1.生成EXCEL文件
        String filePath = vo.getFilePath();
        if(filePath == null || "".equals(filePath)) {
            throw new RuntimeException("记录ID[" + vo.getId() + "]文件路径为空，生成失败！");
        }
        logger.error("开始生成记录ID["+vo.getId()+"]的文件。。。。");

        // 备用变量
        String queryCountSql = vo.getQueryCountSql();
        String queryParam = vo.getQueryParam();
        String dbType = vo.getDbType();

        // 临时文件名
        String timestamp = DateUtils.getCurrDate();
        StringBuilder tempFileNameSb = new StringBuilder();
        tempFileNameSb.append(vo.getFunctionName()).append("_").append(vo.getOperName()).append("_").append(timestamp).append(getFourRandomNum());
        String tempFileName = tempFileNameSb.toString();
        String realZipName = tempFileName + ".zip";

        // SQL查询参数
        Map<String,Object> queryMap = new HashMap<>();
        if(queryParam != null) {
            queryMap = (Map<String, Object>) JSONObject.parse(queryParam);
        }
        //查询总记录数
        int totalCount = getTotalCount(queryCountSql, queryMap, dbType);
        //需要查询的页数
        int  totalPageNum = (totalCount%AsynexportConstants.QUERY_PAGE_COUNT)==0?totalCount/AsynexportConstants.QUERY_PAGE_COUNT:totalCount/AsynexportConstants.QUERY_PAGE_COUNT+1;
        int pageNum = 0;
        int i = 1;

        // 2.生成文件
        ZipOutputStream zipStream = null;
        try {
            File zipFile = new File(filePath + File.separator + tempFileName + ".zip");
            zipStream = new ZipOutputStream(new BufferedOutputStream(new FileOutputStream(zipFile)));
            zipStream.setEncoding("GBK");

            while (pageNum%AsynexportConstants.NEW_EXCEL_PAGE == 0) {
                logger.error("生成第"+i+"个Excel开始！");
                if (pageNum == totalPageNum) {
                    break;
                }
                String fileName = tempFileName + "_" +i++;
                pageNum = generateExcel2Zip(totalPageNum, pageNum, vo, fileName,zipStream);
                logger.error("生成第"+(i-1)+"个Excel结束！");
            }

            if(zipStream != null) {
                zipStream.close();
            }

        } catch (Exception e) {
            throw new RuntimeException("生成文件流出错！",e);
        }

        // 3.保存文件名称，修改状态
        vo.setFileName(realZipName);
        vo.setDealStatus(AsynexportConstants.DEAL_STATUS_SUCCESS);
        vo.setLastDealTime(new Date());
        //vo.setDealCount(vo.getDealCount() + 1);
        asynexportDao.update(vo);
    }

    /**
     * 获取四位随机数
     * @return
     */
    private String getFourRandomNum() {
        String str = "0123456789";
        StringBuilder sb = new StringBuilder();
        for(int i = 0;i < 4;i++) {
            char ch = str.charAt(new Random().nextInt(str.length()));
            sb.append(ch);
        }
        return sb.toString();
    }

    private int generateExcel2Zip(int totalPageNum, int pageNum, AsyncExportInfo vo, String fileName, ZipOutputStream zipStream) {
        // 备用变量
        String querySql = vo.getQuerySql();
        String functionCode = vo.getFunctionCode();
        String queryParam = vo.getQueryParam();
        String dbType = vo.getDbType();

        // SQL查询参数
        Map<String,Object> queryMap = new HashMap<>();
        if(queryParam != null) {
            queryMap = (Map<String, Object>) JSONObject.parse(queryParam);
        }

        SXSSFWorkbook wb = new SXSSFWorkbook(100);
        String[] titleName = asynexportExcelModel.get(functionCode+AsynexportConstants.TITLE_NAME_SUFFIX).split("\\|\\|");
        String sheetName = asynexportExcelModel.get(functionCode+AsynexportConstants.SHEET_NAME_SUFFIX);

        CellStyle columnHeadStyle = wb.createCellStyle();
        columnHeadStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN);// 下边框
        columnHeadStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN);// 左边框
        columnHeadStyle.setBorderRight(HSSFCellStyle.BORDER_THIN);// 右边框
        columnHeadStyle.setBorderTop(HSSFCellStyle.BORDER_THIN);// 上边框
        columnHeadStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
        columnHeadStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
        columnHeadStyle.setWrapText(true);
        Font f = wb.createFont();// 字体
        f.setFontHeightInPoints((short) 9);// 字号
        f.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);//BOLDWEIGHT_BOLD);// 加粗
        columnHeadStyle.setFont(f);

        Sheet sheet = wb.createSheet(sheetName);
        Row row;
        Cell cell;
        row = sheet.createRow(0);
        sheet.createFreezePane(0, 1, 0, 1);
        for(int j=0;j<titleName.length;j++){
            cell = row.createCell(j);
            cell.setCellStyle(columnHeadStyle);
            cell.setCellValue(titleName[j].trim());
            sheet.setColumnWidth(j,  7000);
        }

        // 封装分页信息
        PageInfo pageInfo = new PageInfo();
        //分页查询
        List<Object[]> dataList = new ArrayList();
        int rowIndex = 1;
        try{
            for (int i = pageNum; i < totalPageNum; i++) {
                if(Thread.currentThread().isInterrupted()){
                    throw new InterruptedException();
                }
                //设置分页
                pageInfo.setPageSize(AsynexportConstants.QUERY_PAGE_COUNT);
                pageInfo.setPageNum(++pageNum);
                //分页查询
                logger.error("｛第"+i+"页｝开始查询list。。。");
                dataList = this.query(functionCode, querySql, queryMap, pageInfo, dbType);
                logger.error("｛第"+i+"页｝查询list成功，size="+dataList.size());
                //组装数据到excel
                logger.error("｛第"+i+"页｝组装数据到excel开始。。");
                rowIndex = exportDataToExcelXLSX(sheet, dataList, rowIndex, functionCode);
                logger.error("｛第"+i+"页｝组装数据到excel结束。。");
                //清空list
                dataList.clear();
                if ( pageNum%AsynexportConstants.NEW_EXCEL_PAGE == 0 ) {//达到新建excel记录数
                    break;
                }
            }
            logger.error("｛"+pageNum+"｝把workbook转换为byte数组开始。。");
            byte[] xmlBytes = BytesUtil.convertExcelToBytes(wb);
            logger.error("｛"+pageNum+"｝把workbook转换为byte数组结束。。");
            BytesUtil.buildZipEntry(zipStream,xmlBytes,fileName + ".xlsx");
            logger.error("｛"+pageNum+"｝打包成zip结束。。");
        }catch(Exception e){
            //此处是处理被c3p0吃掉的线程中断状态，以便外层能正常更新记录为失效
            if(e instanceof UncategorizedSQLException){
                Throwable t = e.getCause();
                if(null != t){
                    Throwable cause = t.getCause();
                    if(cause instanceof InterruptedException){
                        Thread.currentThread().interrupt();
                    }
                }
            }
            logger.error(e.getMessage());
            throw new RuntimeException("查询数据信息异常 ",e);
        }
        return pageNum;
    }


    private int exportDataToExcelXLSX(Sheet sheet, List<Object[]> dataList, int rowIndex, String functionCode){
        String[] valueIndex = asynexportExcelModel.get(functionCode+AsynexportConstants.VALUE_INDEX_SUFFIX).split("\\|\\|");
        Row row;
        Cell cell;
        Object obj;
        if(dataList != null && dataList.size() > 0){
            for(Object[] objs : dataList){
                row = sheet.createRow(rowIndex++);
                int index = 0;
                for(int i=0;i<valueIndex.length;i++ ){
                    cell = row.createCell(index++);
                    obj = objs[Integer.parseInt(valueIndex[i].trim())];
                    if (null == obj) {
                       obj = "";
                    }
                    cell.setCellValue(String.valueOf(obj));
                }
            }
        }
        return rowIndex;
    }

    @Override
    public AsyncExportInfo update(AsyncExportInfo vo) {
        asynexportDao.update(vo);
        return (AsyncExportInfo) asynexportDao.find(AsyncExportInfo.class,vo.getId());
    }

    /**
     * 插叙异步导出参数
     * @param operId
     * @param querySql
     * @param queryMap
     * @param functionCodeEnum
     * @throws Exception
     */
    @Override
    public void insertVo(String filePath,String operId,String operName, String queryCountSql, String querySql,
                         Map queryMap, FunctionCodeEnum functionCodeEnum,String dbType,int hashCode) throws Exception {
        AsyncExportInfo asynexportVo = new AsyncExportInfo();
        asynexportVo.setFilePath(filePath);
        asynexportVo.setOperId(operId);
        asynexportVo.setOperName(operName);
        asynexportVo.setOperTs(new Date());
        if(dbType != null) {
            asynexportVo.setDbType(dbType);
        }
        asynexportVo.setQueryCountSql(queryCountSql);
        asynexportVo.setQuerySql(querySql);
        asynexportVo.setQueryParam(JsonUtils.toJson(queryMap));
        asynexportVo.setDealStatus(AsynexportConstants.DEAL_STATUS_WAIT);
        asynexportVo.setDealCount(0);
        asynexportVo.setDownloadCount(0);
        asynexportVo.setFunctionCode(functionCodeEnum.value);
        asynexportVo.setFunctionName(functionCodeEnum.name);
        asynexportVo.setHashCode(hashCode);
        logger.info("【异步导出表】插入新纪录，hashCode = {}", hashCode);
        asynexportDao.save(asynexportVo);
    }

    @Override
    public List<AsyncExportInfo> findWaitGenStatAsynExportList() {
        String hql = "FROM AsyncExportInfo WHERE dealStatus=:dealStatus";
        Map<String,Object> queryParam = new HashMap<>();
        queryParam.put("dealStatus",AsynexportConstants.DEAL_STATUS_WAIT);
        return asynexportDao.findByHql(hql,queryParam,1,3);
    }

    @Override
    public List<AsyncExportInfo> findWaitCleanAsynExportList(String cleanDateStr) {
        String hql = "FROM AsyncExportInfo WHERE dealStatus=:dealStatus AND lastDealTime IS NOT NULL " +
                "AND lastDealTime <=TO_DATE(:lastDealTimeEnd, 'yyyymmddHH24MISS')";
        Map<String,Object> queryParam = new HashMap<>();
        queryParam.put("dealStatus",AsynexportConstants.DEAL_STATUS_SUCCESS);
        queryParam.put("lastDealTimeEnd",cleanDateStr + "235959");
        return asynexportDao.findByHql(hql,queryParam);
    }

    @Override
    public AsyncExportInfo findByHashCodeAndSql(int hashCode, String querySql) {
        if(StringUtils.isBlank(querySql)) {
            return null;
        }
        String hql = "FROM AsyncExportInfo WHERE hashCode = :hashCode and dealStatus in (:dealStatus)";
        Map<String,Object> queryParam = new HashMap<>();
        queryParam.put("hashCode", hashCode);
        queryParam.put("dealStatus", new String[]{AsynexportConstants.DEAL_STATUS_WAIT, AsynexportConstants.DEAL_STATUS_DOING});
        List<AsyncExportInfo> list = asynexportDao.findByHql(hql, queryParam);
        for (AsyncExportInfo info : list){
            if(querySql.equals(info.getQuerySql())){
                return info;
            }
        }
        return null;
    }

    @Override
    @Transactional(rollbackFor = Exception.class)
    public String cancelById(Long id){
        AsyncExportInfo info = (AsyncExportInfo)asynexportDao.find(AsyncExportInfo.class, id);
        if(null == info){
            return "该记录不存在";
        } else if(AsynexportConstants.DEAL_STATUS_SUCCESS.equals(info.getDealStatus())
                    || AsynexportConstants.DEAL_STATUS_FAIL.equals(info.getDealStatus())
                    || AsynexportConstants.DEAL_STATUS_INVALID.equals(info.getDealStatus())) {
            return "该记录已进入终态，不允许操作";
        }
        info.setDealStatus(AsynexportConstants.DEAL_STATUS_INVALID);
        update(info);
        return "取消成功";
    }

    /*public static void main(String[] args) {
        String queryParam = "{\"startDt\":\"20170105 000000\",\"endDt\":\"20170905 235959\"}";

        Map<String,Object> queryMap = new HashMap<>();
        if(queryParam != null) {
            queryMap = (Map<String, Object>) JSONObject.parse(queryParam);
        }

        for (Map.Entry<String,Object> entry :queryMap.entrySet()) {
            System.out.println("KEY:"+entry.getKey());
            System.out.println("VALUE:"+ entry.getValue());
            System.out.println("______________");
        }
    }*/
}
